-- 健康教育活动记录表（增强版，包含附件管理）
CREATE TABLE hc_health_education_activity (
    -- 活动基本信息
    id SERIAL PRIMARY KEY,  -- 活动记录ID
    activity_id VARCHAR(50) UNIQUE,  -- 活动编号（可自定义生成）
    
    -- 活动时间地点
    activity_date DATE NOT NULL,  -- 活动日期
    activity_time TIME,  -- 活动时间
    activity_location VARCHAR(200) NOT NULL,  -- 活动地点
    
    -- 活动基本信息
    activity_form INTEGER NOT NULL,  -- 活动形式
    activity_theme VARCHAR(300) NOT NULL,  -- 活动主题
    organizer VARCHAR(100) NOT NULL,  -- 组织者
    presenter VARCHAR(100),  -- 主讲人
    
    -- 参与人员信息
    participant_category INTEGER NOT NULL,  -- 接受健康教育人员类别
    participant_count INTEGER DEFAULT 0,  -- 接受健康教育人数
    
    -- 资料发放情况
    materials_issued JSONB,  -- 发放资料种类及数量 {材料类型: 数量}
    
    -- 活动内容详情
    activity_content TEXT,  -- 活动内容
    activity_summary TEXT,  -- 活动总结评价
    
    -- 存档材料（使用数组存储存档材料类型）
    archived_materials INTEGER[],  -- 存档材料：1书面材料 2图片材料 3印刷材料 4影音材料 5签到表 6其他材料
    other_materials_desc TEXT,  -- 其他材料说明
    
    -- === 新增：存档材料附件详细信息 ===
    attachment_count INTEGER DEFAULT 0,  -- 附件总数
    total_file_size BIGINT DEFAULT 0,  -- 附件总大小（字节）
    file_storage_path VARCHAR(500),  -- 文件存储路径
    digital_archive_url VARCHAR(500),  -- 数字化存档访问URL
    
    -- 活动分类与评估
    activity_type INTEGER,  -- 活动类型：1发放资料 2宣传栏 3健康咨询 4知识讲座 5个体化教育
    target_population VARCHAR(200),  -- 目标人群描述
    core_information TEXT,  -- 核心信息
    
    -- 资源使用情况
    duration_minutes INTEGER,  -- 活动时长（分钟）
    venue_facilities TEXT,  -- 场地设施情况
    cost_amount DECIMAL(10,2),  -- 活动费用
    
    -- 系统管理字段
    created_by VARCHAR(50) NOT NULL,  -- 填表人
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 填表时间
    reviewed_by VARCHAR(50),  -- 负责人
    reviewed_at TIMESTAMP,  -- 负责人审核时间
    institution_code VARCHAR(20) NOT NULL,  -- 机构编码
    
    -- 索引
    CONSTRAINT fk_hc_institution FOREIGN KEY (institution_code) REFERENCES hc_healthcare_institution(code)
);

-- 新增：健康教育活动附件详情表
CREATE TABLE hc_health_education_attachment (
    id SERIAL PRIMARY KEY,
    activity_id VARCHAR(50) NOT NULL,  -- 关联活动ID
    attachment_type INTEGER NOT NULL,  -- 附件类型：1书面材料 2图片材料 3印刷材料 4影音材料 5签到表 6其他材料
    file_name VARCHAR(255) NOT NULL,  -- 文件名
    file_original_name VARCHAR(255),  -- 文件原始名
    file_path VARCHAR(500) NOT NULL,  -- 文件存储路径
    file_url VARCHAR(500),  -- 文件访问URL
    file_size BIGINT DEFAULT 0,  -- 文件大小（字节）
    file_format VARCHAR(20),  -- 文件格式：pdf, jpg, mp4, doc等
    mime_type VARCHAR(100),  -- MIME类型
    
    -- 附件元数据
    description TEXT,  -- 附件描述
    page_count INTEGER,  -- 页数（针对文档）
    duration_seconds INTEGER,  -- 时长（针对音视频，秒）
    resolution VARCHAR(50),  -- 分辨率（针对图片视频）
    
    -- 数字化处理信息
    is_digitized BOOLEAN DEFAULT FALSE,  -- 是否已完成数字化
    digitized_by VARCHAR(50),  -- 数字化处理人
    digitized_at TIMESTAMP,  -- 数字化时间
    ocr_text TEXT,  -- OCR识别文本内容（针对扫描件）
    
    -- 系统字段
    uploaded_by VARCHAR(50) NOT NULL,  -- 上传人
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 上传时间
    is_active BOOLEAN DEFAULT TRUE,  -- 是否有效
    
    -- 索引和外键
    CONSTRAINT fk_hc_attachment_activity FOREIGN KEY (activity_id) REFERENCES hc_health_education_activity(id) ON DELETE CASCADE
);

-- 新增：存档材料统计表
CREATE TABLE hc_archive_material_statistics (
    id SERIAL PRIMARY KEY,
    institution_code VARCHAR(20) NOT NULL,  -- 机构编码
    statistic_date DATE NOT NULL,  -- 统计日期
    material_type INTEGER NOT NULL,  -- 材料类型
    
    -- 统计信息
    total_activities INTEGER DEFAULT 0,  -- 活动总数
    total_attachments INTEGER DEFAULT 0,  -- 附件总数
    total_file_size BIGINT DEFAULT 0,  -- 总文件大小
    digitized_count INTEGER DEFAULT 0,  -- 已数字化数量
    
    -- 索引
    CONSTRAINT fk_hc_statistics_institution FOREIGN KEY (institution_code) REFERENCES hc_healthcare_institution(code),
    UNIQUE(institution_code, statistic_date, material_type)
);

-- 创建索引
CREATE INDEX idx_hc_health_education_date ON hc_health_education_activity(activity_date);
CREATE INDEX idx_hc_health_education_location ON hc_health_education_activity(activity_location);
CREATE INDEX idx_hc_health_education_form ON hc_health_education_activity(activity_form);
CREATE INDEX idx_hc_health_education_organizer ON hc_health_education_activity(organizer);
CREATE INDEX idx_hc_health_education_type ON hc_health_education_activity(activity_type);
CREATE INDEX idx_hc_health_education_institution ON hc_health_education_activity(institution_code);

-- 附件表索引
CREATE INDEX idx_hc_attachment_activity ON hc_health_education_attachment(activity_id);
CREATE INDEX idx_hc_attachment_type ON hc_health_education_attachment(attachment_type);
CREATE INDEX idx_hc_attachment_uploaded_at ON hc_health_education_attachment(uploaded_at);
CREATE INDEX idx_hc_attachment_digitized ON hc_health_education_attachment(is_digitized);

-- 统计表索引
CREATE INDEX idx_hc_statistics_date ON hc_archive_material_statistics(statistic_date);
CREATE INDEX idx_hc_statistics_institution ON hc_archive_material_statistics(institution_code);

-- 更新字典表，添加附件相关字典
INSERT INTO hc_health_education_dict (dict_type, dict_code, dict_name, description, sort_order) VALUES
('file_format', 1, 'PDF', 'PDF文档', 1),
('file_format', 2, 'DOC', 'Word文档', 2),
('file_format', 3, 'DOCX', 'Word文档', 3),
('file_format', 4, 'JPG', 'JPEG图片', 4),
('file_format', 5, 'PNG', 'PNG图片', 5),
('file_format', 6, 'MP4', 'MP4视频', 6),
('file_format', 7, 'MP3', 'MP3音频', 7),
('file_format', 8, 'XLS', 'Excel表格', 8),
('file_format', 9, 'PPT', 'PowerPoint', 9);

-- 创建附件统计视图
CREATE VIEW hc_activity_attachment_summary AS
SELECT 
    hea.id as activity_id,
    hea.activity_id as activity_code,
    hea.activity_theme,
    hea.activity_date,
    COUNT(hea_att.id) as attachment_count,
    SUM(hea_att.file_size) as total_size,
    COUNT(CASE WHEN hea_att.is_digitized THEN 1 END) as digitized_count,
    ARRAY_AGG(DISTINCT hea_att.attachment_type) as attachment_types
FROM hc_health_education_activity hea
LEFT JOIN hc_health_education_attachment hea_att ON hea_att.activity_id = hea.id AND hea_att.is_active = true
GROUP BY hea.id, hea.activity_id, hea.activity_theme, hea.activity_date;

-- 创建机构存档统计视图
CREATE VIEW hc_institution_archive_stats AS
SELECT 
    hea.institution_code,
    hci.name as institution_name,
    COUNT(DISTINCT hea.id) as total_activities,
    COUNT(hea_att.id) as total_attachments,
    SUM(hea_att.file_size) as total_storage_size,
    COUNT(CASE WHEN hea_att.is_digitized THEN 1 END) as digitized_attachments,
    EXTRACT(YEAR FROM hea.activity_date) as year,
    EXTRACT(MONTH FROM hea.activity_date) as month
FROM hc_health_education_activity hea
JOIN hc_healthcare_institution hci ON hci.code = hea.institution_code
LEFT JOIN hc_health_education_attachment hea_att ON hea_att.activity_id = hea.id AND hea_att.is_active = true
GROUP BY hea.institution_code, hci.name, year, month;

-- 触发器：更新活动表的附件统计信息
CREATE OR REPLACE FUNCTION update_activity_attachment_stats()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新活动表的附件统计
    UPDATE hc_health_education_activity 
    SET 
        attachment_count = (
            SELECT COUNT(*) 
            FROM hc_health_education_attachment 
            WHERE activity_id = COALESCE(NEW.activity_id, OLD.activity_id) 
            AND is_active = true
        ),
        total_file_size = (
            SELECT COALESCE(SUM(file_size), 0)
            FROM hc_health_education_attachment 
            WHERE activity_id = COALESCE(NEW.activity_id, OLD.activity_id) 
            AND is_active = true
        )
    WHERE id = COALESCE(NEW.activity_id, OLD.activity_id);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_attachment_stats
    AFTER INSERT OR UPDATE OR DELETE ON hc_health_education_attachment
    FOR EACH ROW
    EXECUTE FUNCTION update_activity_attachment_stats();

-- 注释
COMMENT ON TABLE hc_health_education_activity IS '健康教育活动记录表（含附件管理）';
COMMENT ON TABLE hc_health_education_attachment IS '健康教育活动附件详情表';
COMMENT ON TABLE hc_archive_material_statistics IS '存档材料统计表';
COMMENT ON COLUMN hc_health_education_activity.attachment_count IS '附件总数';
COMMENT ON COLUMN hc_health_education_activity.total_file_size IS '附件总大小（字节）';
COMMENT ON COLUMN hc_health_education_activity.file_storage_path IS '文件存储根路径';
COMMENT ON COLUMN hc_health_education_activity.digital_archive_url IS '数字化存档访问URL';
COMMENT ON COLUMN hc_health_education_attachment.ocr_text IS 'OCR识别文本内容，便于全文检索';
COMMENT ON COLUMN hc_health_education_attachment.is_digitized IS '是否已完成数字化处理';